<?PHP

include_once 'dbconnect.php';

function getTestimonialsOfUser($userID){

    $db_handle = connect_to_sql();
    $db_found = connect_to_db($db_handle);
    $SQL = "SELECT starrecipients.userRecipientID, starbag.userID AS userSourceID, " .
        "count(testimonials.testimonialEntryID) AS StarCount, testimonials.explanation, testimonials.dateAndTime " .
        "FROM testimonials, starrecipients, starbag " .
        "WHERE testimonials.starRecipientID = starrecipients.starrecipientID AND " .
        "starrecipients.starbagID = starbag.starbagID AND " .
        "starrecipients.userRecipientID = $userID GROUP BY testimonials.dateAndTime";
    $testimonialData = Array();

    if ($db_found){
        $result = mysql_query($SQL);

        if($result === FALSE) {
            die(mysql_error()); // TODO: better error handling
        }

        while ($row = mysql_fetch_array($result)) {
            array_push($starInfo, array($row["userRecipientID"], $row["userSourceID"], $row["StarCount"],
                $row["explanation"], $row["dateAndTime"]));
        }
        mysql_close($db_handle);
    }

    return $testimonialData;
}

function getIdenticalString($specialStr){
    $SQL = "SELECT starSpecialGenID, userID FROM starBag WHERE starSpecialGenID = \"" . $specialStr . "\"";

    return mysql_query($SQL);
}

function getBasicUserInfo($userName, $password) {
//Password is "LEFT(MD5('$password'),20);" because password table in DB has a limit of 20 chars
	$SQL = "SELECT user.userID, user.username, user.nwlID, user.isActive, fullname.firstname, " .
											"fullname.middlename, fullname.lastname " .
											"FROM user, fullname WHERE user.username = $userName " .
											"AND user.password = LEFT(MD5($password),20) AND user.userID = fullname.userID";
	
	return mysql_query($SQL);
}

function getAllExistingActiveUsers(){
    $userName = $_SESSION["userName"];
    $SQL = "SELECT user.userID, user.username, user.nwlID " .
        "FROM user WHERE user.isActive = 1";

    return mysql_query($SQL);
}

function getExistingActiveUsers() {
    $userName = $_SESSION["userName"];
    $SQL = "SELECT user.userID, user.username, user.nwlID " .
                    "FROM user WHERE user.isActive = 1 AND user.username != \"$userName\"";

    return mysql_query($SQL);
}

function getAvailableStarCount(){
    $userName = $_SESSION["userName"];
    $SQL = "SELECT user.userID, count(starBag.starBagID) AS StarCount " .
        "FROM starBag, user where user.userID = starBag.userID and user.username = \"$userName\" ".
        "AND (starBag.starBagID NOT IN (SELECT starRecipients.starBagID FROM starRecipients)) " .
        "GROUP BY user.userID";

    return mysql_query($SQL);
}

function getAvailableStars(){
    $userName = $_SESSION["userName"];
    $SQL = "SELECT user.userID, starBag.starBagID, starBag.starSpecialGenID FROM starBag, user " .
                    "WHERE user.userID = starBag.userID AND user.username = \"$userName\" ".
                    "AND (starbag.starBagID NOT IN (SELECT starRecipients.starBagID FROM starRecipients))";

    return mysql_query($SQL);
}

function getTotalStarCountOwnedByUser($userID){
    $SQL = "SELECT starRecipients.userRecipientID, count(testimonials.testimonialEntryID) AS StarCount " . "
            FROM starRecipients, testimonials WHERE starRecipients.userRecipientID = $userID " .
        "AND starRecipients.starRecipientID = testimonials.starRecipientID " .
        "GROUP BY starRecipients.userRecipientID";

    return mysql_query($SQL);
}

function giveStar($recipientID, $comment, $starBagID, $date){
    $conn = connect_to_sqli();

    if(!$conn) {
        die("Connection Failed: " . mysqli_connect_error());
    }

    $SQL = "INSERT INTO starRecipients (starBagID, userRecipientID) VALUES ($starBagID, $recipientID)";
    echo "This is the value:";

    if (mysqli_query($conn, $SQL)){
        $lastID = mysqli_insert_id($conn);
        $SQL = "INSERT INTO testimonials (starRecipientID, dateAndTime, explanation) "
                . "VALUES ($lastID, \"" . $date . "\", " . "\"$comment\")";
        if (mysqli_query($conn, $SQL)){
            echo "SUCCESS!";
        }
        else {
            echo "Error: " . $SQL . "<br>" . mysqli_error($conn) . "Please ROLLBACK SQL";
        }
    }

    mysqli_close($conn);
}

function insertStarToUser($userID, $starSpecialGenID){
    $conn = connect_to_sqli();
    $date = date("Y-m-d H:i:s");
    echo $date;
    if(!$conn) {
        die("Connection Failed: " . mysqli_connect_error());
    }

    $SQL = "INSERT INTO starBag (starSpecialGenID, userID) "
            . "VALUES (\"" . $starSpecialGenID . "\", $userID)";

    if (!mysqli_query($conn, $SQL)){
        echo "Error: " . $SQL . "<br>" . mysqli_error($conn) . "Please ROLLBACK SQL";
    }
    mysqli_close($conn);
}

?>